'use strict';

const util = require("util");
const Service = require('egg').Service;
const mysql = require('mysql');

// const Koa = require('koa');
// const app = new Koa();
// const bodyParser = require('koa-bodyparser');

// // 使用ctx.body解析中间件
// app.use(bodyParser());

class equipmentService extends Service {
    //查询所有仪器信息的方法
    async  findEquipmentAll() {
        const result = await this.app.mysql.select('e_equipments')
        return {
            //查询成功，并返回数据
            code: 200,
            data: result,
            message: '查询成功！',
        };
    }

    //根据设备编号修改设备信息
    async updateEquipmentByNumber(equipment_number, equipment_name, equipment_price) {
        //查询的条件：设备编号equipment_number
        const options = {
            where: {
                equipment_number: equipment_number,
            }
        };
        //要修改的数据：设备名称和设备价格
        const row = {
            equipment_name: equipment_name,
            equipment_price: equipment_price,
        };
        let res = await this.app.mysql.get('e_equipments', { equipment_number: equipment_number });
        //判断该设备编号是否存在
        if (res == null) {
            //存在直接返回更新失败
            return {
                code: 500,
                data: res,
                message: '更新失败，此设备不存在',
            };
        } else {
            //不存在首先进行更新信息
            if (equipment_name && equipment_price ) {
                console.log(equipment_name);
                const result = await this.app.mysql.update('e_equipments', row, options);
                res = await this.app.mysql.get('e_equipments', { equipment_number: equipment_number });
                return {
                    //更新完成之后返回状态吗、数据、提示消息
                    code: 200,
                    data: res,
                    message: '更新设备信息成功！',
                };
            }else {
                return {
                    //更新失败
                    code: 200,
                    data: {},
                    message: '更新失败，请检查您输入的信息！',
                };
            }
        }
    }

    //根据设备编号查找设备
    async findEquipmentByNumber(equipment_number) {
        //查询该设备编号是否存在
        const result = await this.app.mysql.get('e_equipments', { equipment_number: equipment_number });
        if (result == null) {
            //不存在返回500，查询失败
            return {
                code: 500,
                data: result,
                message: '查询失败',
            }
        } else {
            //存在返回200，查询成功
            return {
                code: 200,
                data: result,
                message: '查询成功',
            }
        }
    }

    //根据设备名查找设备
    async findEquipmentByName(equipment_name) {
        const result = await this.app.mysql.select('e_equipments', { // 搜索e_equipments表
            // WHERE 条件
            where: { equipment_name: equipment_name },
            // 要查询的表字段
            columns: ['equipment_id', 'equipment_name', 'equipment_type', 'equipment_manufactor', 'equipment_model', 'equipment_price', 'equipment_number'],
        });
        console.log(result);
        if (result.length == 0) {
            //查询失败返回500
            return {
                code: 500,
                data: result,
                message: '查询失败,设备不存在',
            }
        } else {
            //查询成功返回200
            return {
                code: 200,
                data: result,
                message: '查询成功',
            }
        }
    }

    //查询数据记录总条数
    async findEquipmentAllCount() {
        const result = await this.app.mysql.query('SELECT COUNT(*) count FROM e_equipments');
        console.log(result);
        return result[0].count;
    }

    //查询设备名数据记录总条数
    async findEquipmentCountByName(equipment_name) {
        const sql = 'SELECT COUNT(*) count FROM e_equipments WHERE equipment_name = ?';
        const result = await this.app.mysql.query(sql, [equipment_name]);
        return result[0].count;
    }

    //新增仪器
    async insertEquipment(postData) {
        //首先判断设备编号是否存在
        const res = await this.service.equipment.findEquipmentByNumber(postData.equipment_number);
        //存在则提示添加失败，主键重复，并且返回500
        if (res.data != null) {
            return {
                code: 500,
                data: res.data,
                message: '添加失败，此设备已存在',
            }
        } else {//不存在再进一步判断受影响的行数
            //执行插入数据
            const result = await this.app.mysql.insert('e_equipments', postData);
            //修改的行数等于说明修改成功
            if (result.affectedRows === 1) {
                return {
                    code: 200,
                    data: postData,
                    message: '添加成功!'
                };
            } else {
                //否则失败
                return {
                    code: 500,
                    data: {},
                    message: '添加失败!',
                }
            }
        }
    }
    //根据设备编号删除仪器
    async deleteEquipmentByNumber(equipment_number) {
        const result = await this.app.mysql.delete('e_equipments', {
            equipment_number: equipment_number,
        });
        //修改的行数>0则删除成功
        if (result.affectedRows > 0) {
            //删除成功返回200，删除成功
            return {
                code: 200,
                data: {},
                message: '删除成功',
            }
        } else {
            //删除失败返回500
            return {
                code: 500,
                data: {},
                message: '删除失败,设备不存在！'
            }
        }
    }

    //按照指定名称分页查询符合条件的设备
    async findEquipmentPaging(postData) {
        //每页展示几条数据
        const limit = 3;
        //从第几条数据开始
        let offset = postData.page * limit - limit;
        //数据库总设备条数
        const count = await this.ctx.service.equipment.findEquipmentCountByName(postData.equipment_name);
        console.log(count);
        //count[0].count拿到[ RowDataPacket { count: 13 } ]里面的总数13
        const pageSize = Math.ceil(count / limit);
        //判断用户输入的页码是否符合要求
        if (pageSize >= postData.page & postData.page > 0) {
            //如果用户查询为最后一页，不重复展示设备信息
            if (pageSize === postData.page) {
                offset = postData.page * limit - limit;
                console.log(offset);
            }
            let sql = 'SELECT * FROM e_equipments WHERE equipment_name = ? limit ? offset ?';
            const result = await this.app.mysql.query(sql, [postData.equipment_name, limit, offset]);
            //返回成功的数据
            return {
                code: 200,
                data: result,
                message: '查询成功:查询的第' + postData.page + '页数据如上',
            }
        } else if ((pageSize < postData.page)) {
            return {
                //返回查询失败，页码超出范围
                code: 500,
                data: {},
                message: '查询失败:您输入的页码超出范围，请重新输入!',
            }
        } else {
            return {
                //返回查询失败：输入的页码小于0
                code: 600,
                data: {},
                message: '查询失败：您输入的页码不正确（小于0），请重新输入!',

            }
        }
    }

}

module.exports = equipmentService;